/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Comment;
import java.sql.*;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import util.DBUtil;

/**
 *
 * @author NHAN
 */
public class CommentDAO {

    Connection conn;
    String sqlSelectByID = "select * from Comment where id=?";
    String sqlCreate = "insert into Comment(post_id, content, created_by,modified_date) values(?, ?, ?,?)";
    String sqlUpdate = "update Comment set post_id=?, content= ?, created_by=?,modified_date=?";
    String sqlDelete = "delete from Comment where id=?";
    Logger logger = Logger.getLogger(CommentDAO.class);

    public CommentDAO() {
    }

    /**
     * get a Comment by specifying identifier value
     *
     * @param id
     * @return Comment object
     */
    public Comment getComment(int id) {
        Comment c = new Comment();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//post_id, content, created_by,modified_date
            PreparedStatement ps = conn.prepareStatement(sqlSelectByID);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                c.setPostId(rs.getInt("post_id"));
                c.setContent(rs.getString("content"));
                c.getAuthor().setFirstName(rs.getString("created_by"));
                c.setModifiedDate(rs.getDate("modified_date"));
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return c;
    }

    /**
     *
     * @param currentPage current page to select
     * @param pageSize number of records per page
     * @return list of Comments
     */
    public ArrayList<Comment> getComments(int currentPage, int pageSize) {
        ArrayList<Comment> list = new ArrayList<Comment>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Comment", "id", "id");
            qo.setColumns("id, post_id, content, created_by,modified_date");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);

            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Comment c = new Comment();
                c.setId(rs.getInt(1));
                c.setPostId(rs.getInt(2));
                c.setContent(rs.getString(3));
                c.getAuthor().setFirstName(rs.getString(4));
                c.setModifiedDate(rs.getDate(5));
                list.add(c);
            }

        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    /**
     * add new Comment
     *
     * @param c Comment
     * @return greater than 0 value if the action is success, else return 0
     */
    public int create(Comment c) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setInt(1, c.getPostId());
            ps.setString(2, c.getContent());
            ps.setString(3, c.getAuthor().getFirstName());
            ps.setDate(4, new java.sql.Date(c.getModifiedDate().getTime()));
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
        } catch (Exception ex) {
        }
        return effectedRows;
    }

    /**
     * update specified Comment
     *
     * @param c Comment to update
     * @return 1 or greater if action is successful, else return 0
     */
    public int update(Comment c) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setInt(1, c.getPostId());
            ps.setString(2, c.getContent());
            ps.setString(3, c.getAuthor().getFirstName());
            ps.setDate(4, new java.sql.Date(c.getModifiedDate().getTime()));
            ps.setInt(5, c.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
        } catch (Exception ex) {
        }
        return effectedRows;
    }

    /**
     * delete a Comment by specifying identifier value
     *
     * @param id
     * @return 1 or greater value if action is successful , else return 0
     */
    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
        } catch (Exception ex) {
        }
        return effectedRows;
    }
}
